﻿Imports System.Data
Imports System.Data.Odbc


Module dataEliminaPago
    Dim capVariable As Integer
    Dim VectorDepartamento(16, 5) As String
    Sub datosEPCBCargaBlock()

        conectar()
        Dim dt2 As New DataTable

        cmd = New OdbcCommand("SELECT ID, Nombre FROM  tblock", con)
        dr = cmd.ExecuteReader
        'executing the command and assigning it to connection 

        dt2.Load(dr, LoadOption.OverwriteChanges)
        With frmEliminarPago.cbBlock
            .DataSource = dt2
            .DisplayMember = dt2.Columns.Item(1).Caption
            .ValueMember = dt2.Columns.Item(0).Caption
        End With
        ' listaDepartamentos(frmDepartamento.cbSelBlock.SelectedValue)
        desconectar()

    End Sub


    Function listadoEPCargaDepartamento(ByVal IDBlock As Integer)
        Dim i As Integer
        Dim depa As String
        frmEliminarPago.cbDepartamento.Items.Clear()
        conectar()

        cmd.Connection = con
        cmd.CommandText = "SELECT d.ID,d.IDBlock,d.NumeroActual,d.NumeroAnterior,p.Nombre,p.ApPaterno,p.ApMaterno "
        cmd.CommandText += "FROM tDepartamento d INNER JOIN tPersonas p ON p.ID=d.IDPropietario "
        cmd.CommandText += "WHERE p.IDTipoRelacion=1 AND d.IDBlock=" + IDBlock.ToString
        cmd.CommandType = CommandType.Text

        dr = cmd.ExecuteReader
        If dr.HasRows Then
            While dr.Read()
                VectorDepartamento(i, 0) = dr("ID").ToString
                VectorDepartamento(i, 1) = dr("IDBlock").ToString
                VectorDepartamento(i, 2) = dr("NumeroActual").ToString
                VectorDepartamento(i, 3) = dr("NumeroAnterior").ToString
                VectorDepartamento(i, 4) = dr("Nombre").ToString + " " + dr("ApPaterno").ToString + " " + dr("ApMaterno").ToString
                depa = Trim(dr("NumeroActual").ToString) & " EX " & Trim(dr("NumeroAnterior").ToString)
                frmEliminarPago.cbDepartamento.Items.Add(Trim(depa))
                i += 1
                depa = ""
            End While
        End If
        dr.Close()
        desconectar()
        Return VectorDepartamento
    End Function

    Sub EliminarPago(ByVal idEli As Integer)

        Try


            conectar()
            cmd = New OdbcCommand("UPDATE tdeudacuotas set  IDPagosRecibidos= NULL WHERE  IDPagosRecibidos ='" & idEli & "' ", con)
            cmd.ExecuteNonQuery()

            cmd = New OdbcCommand("DELETE FROM  tpagosrecibidos WHERE  ID  IN ('" & idEli & "')", con)
            If cmd.ExecuteNonQuery() Then
                frmEliminarPago.lblMensaje.Visible = True
                datosEPCBCargaBlock()
                CargarNID(capVariable)
                frmEliminarPago.cbDepartamento.Refresh()

            Else
                MessageBox.Show(msjNoEliminar, msjTituloModal, MessageBoxButtons.OK, MessageBoxIcon.Error)
            End If




            desconectar()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub

    Sub CargarNID(ByVal idDepa As Integer)
        conectar()
        Dim dt2 As New DataTable
        capVariable = idDepa
        cmd = New OdbcCommand("Select p.ID From tpagosrecibidos p, tdeudaarrastre a,  tdeudacuotas d WHERE a.IDDepartamento ='" & idDepa & "' AND a.ID =  d.IDDeudaArrastre AND d.IDPagosRecibidos = p.ID GRoup by p.ID UNION Select p.ID From tpagosrecibidos p, tGastosComunes g WHERE g.IDDepartamentos = '" & idDepa & "' AND p.ID= g.IDPago GRoup by p.ID", con)
        dr = cmd.ExecuteReader
            'executing the command and assigning it to connection 

        dt2.Load(dr, LoadOption.OverwriteChanges)
        With frmEliminarPago.cbPago
            .DataSource = dt2
            .DisplayMember = dt2.Columns.Item(0).Caption
            .ValueMember = dt2.Columns.Item(0).Caption
            End With
            ' listaDepartamentos(frmDepartamento.cbSelBlock.SelectedValue)
        desconectar()

    End Sub

End Module
